{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "440bde29-6c37-4c86-a5f5-12ab7cdaa844",
   "metadata": {},
   "source": [
    "## A04 Validate the Data\n",
    "\n",
    "This code is used for data quality verification and basic data analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04e84187-17d5-41f6-829e-4c2ac92c2918",
   "metadata": {},
   "source": [
    "### 1. init spark session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "4b236933-c7c9-447a-a5cc-1743759f414e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import findspark\n",
    "findspark.init()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "659686fe-8a37-462c-9b6a-b966f63fcf0f",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "spark = SparkSession.builder.appName(\"unemployment data\").getOrCreate()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba128c91-00a7-409c-969d-22a43b0b79eb",
   "metadata": {},
   "source": [
    "### 2. read formatted data and exploitation data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "00c5709d-d95d-4291-8b8e-c50a84212713",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_formatted = spark.read.parquet(\"./Formatted Zone/unemployment.parquet\")\n",
    "df = spark.read.parquet(\"./Exploitation Zone/unemployment.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "548db6da-4b8f-41ad-9148-7c3585fc78a8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1200"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_formatted.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "2dc55002-a7fa-4a62-95f6-56c6e2fbec30",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1200"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17449b15-10cf-4af6-984f-fded36418072",
   "metadata": {},
   "source": [
    "The number of rows is the same before and after data cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "35d39145-d025-4ad6-bfa9-c5735d808047",
   "metadata": {},
   "source": [
    "### 3. Missing Values Statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "fc1d2e80-5925-47ee-bd19-bff50d0b61d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import when, col, sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "145ae445-f2cd-4080-ac77-40284f67e50c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+----------+--------------+----------------+---+---------+-------------+------+----+---+\n",
      "|Any|Codi_Barri|Codi_Districte|Demanda_ocupacio|Mes|Nom_Barri|Nom_Districte|Nombre|Sexe|_id|\n",
      "+---+----------+--------------+----------------+---+---------+-------------+------+----+---+\n",
      "|  0|         0|             0|               0|  0|        0|            0|     0|   0|  0|\n",
      "+---+----------+--------------+----------------+---+---------+-------------+------+----+---+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "missing_values_df = df.select(\n",
    "    [sum(when(col(column).isNull(), 1).otherwise(0)).alias(column) for column in df.columns]\n",
    ")\n",
    "\n",
    "missing_values_df.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a6d89e1b-d153-4b74-af83-e6371dfbd8e4",
   "metadata": {},
   "source": [
    "### 4. Statistics of the number of data entries in different columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "2a7cc229-14e5-4114-9742-eab1d43faf48",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- Any: integer (nullable = true)\n",
      " |-- Codi_Barri: integer (nullable = true)\n",
      " |-- Codi_Districte: integer (nullable = true)\n",
      " |-- Demanda_ocupacio: string (nullable = true)\n",
      " |-- Mes: integer (nullable = true)\n",
      " |-- Nom_Barri: string (nullable = true)\n",
      " |-- Nom_Districte: string (nullable = true)\n",
      " |-- Nombre: integer (nullable = true)\n",
      " |-- Sexe: string (nullable = true)\n",
      " |-- _id: long (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "f6174031-0ab1-418e-adda-ce55377c7f67",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-----+\n",
      "| Any|count|\n",
      "+----+-----+\n",
      "|2018|  100|\n",
      "|2023|  100|\n",
      "|2022|  100|\n",
      "|2013|  100|\n",
      "|2014|  100|\n",
      "|2019|  100|\n",
      "|2020|  100|\n",
      "|2012|  100|\n",
      "|2016|  100|\n",
      "|2011|  100|\n",
      "|2017|  100|\n",
      "|2021|  100|\n",
      "+----+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.groupby(\"Any\").count().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "d862408a-2910-417c-9794-0ccae5afd710",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+---+-----+\n",
      "| Any|Mes|count|\n",
      "+----+---+-----+\n",
      "|2021|  5|    1|\n",
      "|2018|  1|  100|\n",
      "|2014|  1|  100|\n",
      "|2019|  1|  100|\n",
      "|2016|  1|  100|\n",
      "|2020|  1|  100|\n",
      "|2012|  1|  100|\n",
      "|2022|  1|  100|\n",
      "|2021|  1|   99|\n",
      "|2013|  1|  100|\n",
      "|2011|  1|  100|\n",
      "|2017|  1|  100|\n",
      "|2023|  1|  100|\n",
      "+----+---+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.groupby(\"Any\", \"Mes\").count().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "40946ccb-dbcf-4b2f-b34c-b2854aafaea6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------+-------------------+-----+\n",
      "|Codi_Districte|      Nom_Districte|count|\n",
      "+--------------+-------------------+-----+\n",
      "|             2|           Eixample|  144|\n",
      "|             1|       Ciutat Vella|   96|\n",
      "|             9|        Sant Andreu|   84|\n",
      "|            99|          No consta|   13|\n",
      "|             3|     Sants-Montjuïc|  192|\n",
      "|             4|          Les Corts|   72|\n",
      "|             5|Sarrià-Sant Gervasi|  127|\n",
      "|             8|         Nou Barris|  156|\n",
      "|             7|     Horta-Guinardó|  132|\n",
      "|            10|         Sant Martí|  124|\n",
      "|             6|             Gràcia|   60|\n",
      "+--------------+-------------------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.groupby(\"Codi_Districte\", \"Nom_Districte\").count().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "810f3764-c293-443e-94fb-f0b7619ef69e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------+--------------+----------+--------------------+-----+\n",
      "|Codi_Districte| Nom_Districte|Codi_Barri|           Nom_Barri|count|\n",
      "+--------------+--------------+----------+--------------------+-----+\n",
      "|             9|   Sant Andreu|        59|       el Bon Pastor|   12|\n",
      "|             1|  Ciutat Vella|         4|Sant Pere, Santa ...|   24|\n",
      "|             2|      Eixample|         6|  la Sagrada Família|   24|\n",
      "|             9|   Sant Andreu|        58|       Baró de Viver|   12|\n",
      "|            10|    Sant Martí|        69|Diagonal Mar i el...|   12|\n",
      "|             8|    Nou Barris|        45|               Porta|   12|\n",
      "|             7|Horta-Guinardó|        33|    el Baix Guinardó|   12|\n",
      "|             2|      Eixample|         7|la Dreta de l'Eix...|   24|\n",
      "|             8|    Nou Barris|        48|        la Guineueta|   12|\n",
      "|            10|    Sant Martí|        73| la Verneda i la Pau|   12|\n",
      "|             6|        Gràcia|        31|   la Vila de Gràcia|   12|\n",
      "|             7|Horta-Guinardó|        39|Sant Genís dels A...|   12|\n",
      "|             8|    Nou Barris|        54|          Torre Baró|   12|\n",
      "|             8|    Nou Barris|        50|        les Roquetes|   12|\n",
      "|             4|     Les Corts|        19|           les Corts|   24|\n",
      "|             7|Horta-Guinardó|        34|            Can Baró|   12|\n",
      "|             6|        Gràcia|        28|Vallcarca i els P...|   12|\n",
      "|             3|Sants-Montjuïc|        15|         Hostafrancs|   24|\n",
      "|            10|    Sant Martí|        65|             el Clot|   12|\n",
      "|             3|Sants-Montjuïc|        11|        el Poble Sec|   24|\n",
      "+--------------+--------------+----------+--------------------+-----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.groupby(\"Codi_Districte\", \"Nom_Districte\", \"Codi_Barri\", \"Nom_Barri\").count().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "da056371-b247-4d9f-b550-a3a94072855d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+------------------+\n",
      "|summary|            Nombre|\n",
      "+-------+------------------+\n",
      "|  count|              1200|\n",
      "|   mean| 566.2558333333334|\n",
      "| stddev|477.82589580818353|\n",
      "|    min|                 0|\n",
      "|    max|              3333|\n",
      "+-------+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.describe(\"Nombre\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "fb14f81c-8ad5-4218-8943-149414e26205",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+-----+\n",
      "| Sexe|count|\n",
      "+-----+-----+\n",
      "|Dones|  403|\n",
      "|Homes|  797|\n",
      "+-----+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.groupby(\"Sexe\").count().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a41153b4-7beb-460d-a24b-d2ccbd94149d",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.19"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
